package com.linln.admin.sync.service.impl;

import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.linln.admin.app.constants.AppConstants;
import com.linln.admin.app.domain.AppVersion;
import com.linln.admin.app.domain.VersionMember;
import com.linln.admin.app.repository.VersionRepository;
import com.linln.admin.sync.domain.FlowSql;
import com.linln.admin.sync.repository.FlowSqlRepository;
import com.linln.admin.sync.service.FlowSqlService;
import com.linln.common.data.PageSort;
import com.linln.common.enums.StatusEnum;
import com.linln.common.utils.HttpServletUtil;
import com.linln.component.shiro.ShiroUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Optional;

/**
 * @author taofucheng
 * @date 2021/11/26
 */
@Service
public class FlowSqlServiceImpl implements FlowSqlService {

    @Autowired
    private FlowSqlRepository flowSqlRepository;
    @Autowired
    private VersionRepository versionRepository;

    /**
     * 根据ID查询数据
     *
     * @param id 主键ID
     */
    @Override
    public FlowSql getById(Long id) {
        return flowSqlRepository.findById(id).orElse(null);
    }

    /**
     * 获取分页列表数据
     *
     * @param example 查询实例
     * @return 返回分页数据
     */
    @Override
    public Page<FlowSql> getPageList(Example<FlowSql> example) {
        // 创建分页对象
        PageRequest page = PageSort.pageRequest();
        FlowSql flowSql = example.getProbe();

        return flowSqlRepository.findAll(new Specification<FlowSql>() {

            public Predicate toPredicate(Root<FlowSql> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                List<Predicate> preList = new ArrayList<>();
                if (StringUtils.isNotBlank(flowSql.getTitle())) {
                    preList.add(cb.like(root.get("title").as(String.class), "%" + flowSql.getTitle() + "%"));
                }
                if (flowSql.getVersion_id() != null) {
                    preList.add(cb.equal(root.get("version_id").as(Long.class), flowSql.getVersion_id()));
                }else{
                    if (!ShiroUtil.isAdmin()) {//不是管理员，只能查看自己创建信息
                        Subquery<VersionMember> subquery = query.subquery(VersionMember.class);
                        //子查询 要查询哪个实体
                        Root<VersionMember> subRoot = subquery.from(VersionMember.class);
                        //子查询要查询哪个字段
                        subquery = subquery.select(subRoot.get("version_id"));
                        subquery.where(subRoot.get("user_id").in(ShiroUtil.getSubject().getId()));
                        //将子查询与父查询和到一起
                        preList.add(cb.and(root.get("version_id").in(subquery)));
                    }
                }
                if (StringUtils.isNotBlank(flowSql.getContent())) {
                    preList.add(cb.like(root.get("content").as(String.class), "%" + flowSql.getContent() + "%"));
                }
                try {
                    String published = HttpServletUtil.getParameter("published", "");
                    if (StringUtils.isNotBlank(published)) {
                        //创建子查询，返回的类型
                        Subquery<AppVersion> subquery = query.subquery(AppVersion.class);
                        //子查询 要查询哪个实体
                        Root<AppVersion> subRoot = subquery.from(AppVersion.class);
                        //子查询要查询哪个字段
                        subquery = subquery.select(subRoot.get("id"));
                        subquery.where(subRoot.get("published").in(published));
                        //将子查询与父查询和到一起
                        preList.add(cb.and(root.get("version_id").in(subquery)));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }

                Predicate[] pres = new Predicate[preList.size()];
                return query.where(preList.toArray(pres)).getRestriction();
            }

        }, page);
    }

    /**
     * 保存数据
     *
     * @param flowSql 实体对象
     */
    @Override
    public FlowSql save(FlowSql flowSql) {
        flowSql.setCreate_id(ShiroUtil.getSubject().getId());
        flowSql.setCreate_time(new Date());
        if (isPublished(flowSql)) {
            throw new IllegalArgumentException("已经发布的SQL不能修改！");
        }
        return flowSqlRepository.save(flowSql);
    }

    /**
     * 状态(启用，冻结，删除)/批量状态处理
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean updateStatus(StatusEnum statusEnum, List<Long> idList) {
        return flowSqlRepository.updateStatus(statusEnum.getCode(), idList) > 0;
    }

    @Override
    public String sync(String combinePubEnv) {
        if (StringUtils.isBlank(combinePubEnv)) {
            throw new IllegalArgumentException("请选择要同步的环境");
        }

        ObjectMapper om = new ObjectMapper();
        StringBuilder sb = new StringBuilder();
        int page = 0;
        while (true) {
            Page<FlowSql> lines = flowSqlRepository.findAll(PageRequest.of(page, 10, Sort.Direction.ASC, "id"));
            if (!lines.hasContent()) {
                break;
            }
            for (FlowSql line : lines) {
                if (!isPublished(line)) {
                    continue;//还没发布的，不可以执行SQL
                }
                List<String> synced = null;
                try {
                    synced = om.readValue(line.getSynced(), new TypeReference<List<String>>() {
                    });
                } catch (Exception e) {
                    synced = new ArrayList<>();
                }
                if (synced.contains(combinePubEnv)) {//如果该环境已经同步过，则不再同步
                    continue;
                }
                synced.add(combinePubEnv);

                sb.append(line.getContent() + "\n");
                List<String> execSqls = new ArrayList<>();
                addSql(line.getContent(),execSqls);
                //TODO 执行execSqls中的SQL

                // 更新状态
                try {
                    line.setSynced(om.writeValueAsString(synced));
                } catch (Exception e) {
                }
                flowSqlRepository.saveAndFlush(line);
            }
            ++page;
        }
        return sb.toString();
    }

    private boolean isPublished(FlowSql flowSql) {
        Optional<AppVersion> version = versionRepository.findById(flowSql.getVersion_id());
        return version.isPresent() ? AppConstants.YES.equals(version.get().getPublished()) : false;
    }

    /**
     * 将一行行的内容转换为对应的可执行SQL
     *
     * @param sql 所有的SQL文本
     * @param exeSql 解析出来的可执行的SQL
     */
    private static void addSql(String sql, List<String> exeSql) {
        sql = StringUtils.trim(sql);
        if (StringUtils.isEmpty(sql)) {
            return;
        }
        StringBuffer sqlLine = new StringBuffer();
        boolean isComment = false;
        boolean isBlock = false;
        for (String s : sql.split("\n")) {
            s = StringUtils.trim(s);
            // SQL正文
            if (StringUtils.isEmpty(s) || s.startsWith("#") || s.startsWith("-- ")
                    || (s.startsWith("/*") && s.replace(";", "").replace("$", "").trim().endsWith("*/"))) {
                // 如果是空行或单行注释，则去除
                continue;
            }
            if (s.startsWith("/*")) {// 多行注释开始
                isComment = true;
                continue;
            }
            if (isComment && s.replace(";", "").replace("$", "").trim().endsWith("*/")) {// 多行注释的结束行
                isComment = false;
                continue;
            }
            if (isComment) {// 如果是注释，则去除
                continue;
            }
            if (!isBlock && (s.trim().toUpperCase().startsWith("DELIMITER ")
                    || s.trim().toUpperCase().startsWith("DELIMITER;")
                    || s.trim().toUpperCase().startsWith("DELIMITER$$"))) {
                isBlock = true;
                continue;
            }
            if (sqlLine != null) {
                if (s.endsWith("$$")) {
                    s = s.substring(0, s.length() - 2) + ";";
                }
                if (s.trim().toUpperCase().startsWith("DELIMITER ") || s.trim().toUpperCase().startsWith("DELIMITER;")
                        || s.trim().toUpperCase().startsWith("DELIMITER$$")) {
                    exeSql.add(sqlLine.toString());
                    sqlLine = new StringBuffer();
                    isBlock = false;
                } else if (isBlock) {
                    sqlLine.append(s);
                    sqlLine.append("\n");
                } else {
                    sqlLine.append(s);
                    sqlLine.append("\n");
                    if (s.endsWith(";")) {// SQL语句已经结束
                        exeSql.add(sqlLine.toString());
                        sqlLine = new StringBuffer();
                        isBlock = false;
                    }
                }
            }
        }
        // 考虑如果sqlLine中有内容的情况
        if (sqlLine != null && sqlLine.length() > 0) {
            exeSql.add(sqlLine.toString().trim());
        }
    }
}